/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package DataBase;

import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
/**
 *
 * @author kossel
 */
public class AdminDB {
    private AdminConexion adminCon;
    private static AdminDB adminDB;
    Connection conn;    

    private AdminDB(){
        adminCon= new AdminConexion();
    }

    public static AdminDB obtenerInstancia(){
        if(adminDB==null){
            adminDB = new AdminDB();
        }
        return adminDB;
    }

    public PreparedStatement prepareTheStatementForQuery(String sql, List<Object> params){
        try {
            PreparedStatement ps;
            this.conn = adminCon.obtenerConnection();
            ps = this.setParams(conn.prepareStatement(sql), params);
            return ps;
        } catch (SQLException ex) {
            Logger.getLogger(AdminDB.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }

    public int doUpdate(String sql, List<Object> params){
        PreparedStatement ps=null;
        try {
            ps = this.setParams(this.conn.prepareStatement(sql),params);
            return ps.executeUpdate();
        } catch (SQLException ex) {
           Logger.getLogger(AdminDB.class.getName()).log(Level.SEVERE, null, ex);
           this.closeConn(null, ps);
           return 0;
        }

    }

    public int doUpdateWithKey(String sql, List<Object> params){
        PreparedStatement ps=null;
        ResultSet rs=null;
        try {
            ps = this.conn.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
            ps=this.setParams(ps, params);
            ps.executeUpdate();
            rs = ps.getGeneratedKeys();
            rs.next();
            return rs.getInt(1);
          
        } catch (SQLException ex) {
            Logger.getLogger(AdminDB.class.getName()).log(Level.SEVERE, null, ex);
            this.closeConn(rs, ps);
            return 0;
        }
    }

    public void ejecutarProcedure(String procedimiento){
        try {
            CallableStatement cs = null;
            cs = this.conn.prepareCall("call " + procedimiento);
            ResultSet rs = cs.executeQuery();
        } catch (SQLException ex) {
            Logger.getLogger(AdminDB.class.getName()).log(Level.SEVERE, null, ex);
        }

    }

    public <T>List<T> ejecutarSeleccion(PreparedStatement ps, Class<?> clase){
        List<Object> tabla=null;
        ResultSet rs=null;
        try {
            System.out.println( ps.toString());
             rs=ps.executeQuery();
            tabla=new ArrayList<Object>();
            while(rs.next()){
                tabla.add(crearObjetos(clase, rs));
            }
            closeConn(rs,ps);
            adminCon.liberarConnextion(conn);
            return (List<T>)tabla;
        }catch (Exception ex) {
            Logger.getLogger(AdminDB.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }finally{
        closeConn(rs,ps);
        conn=null;
        }

    }

    /**********************TRANSACCIONES************************************/
    public void iniciar() throws SQLException{
            this.conn=adminCon.obtenerConnection();
             conn.setAutoCommit(false);
    }
    public void finalizar() throws SQLException{
        conn.commit();
        conn.setAutoCommit(true);
        conn.close();
    }

    public void rollBack(){
        try {
            conn.rollback();
            conn.setAutoCommit(true);
            conn.close();
            conn=null;
        } catch (SQLException ex) {
           Logger.getLogger(AdminDB.class.getName()).log(Level.SEVERE, null, ex);
            conn=null;
        }finally{
            conn=null;
        }
    }


        /******************* ACONTINUACION SE PRESENTAN METODOS PRIVADOS *****************************/

    private PreparedStatement setParams(PreparedStatement ps, List<Object> params) throws SQLException{
        int x=1;
        if(params!=null){
        for(Object param:params){
            if(param==null)ps.setNull(x,java.sql.Types.JAVA_OBJECT);
            else if(param.toString().equals(""))ps.setNull(x,java.sql.Types.JAVA_OBJECT);
            else if(param.getClass().getSimpleName().equals("String")) ps.setString(x, param.toString());
            else if(param.getClass().getSimpleName().equals("Integer")) ps.setInt(x,Integer.parseInt(param.toString()));
            else if(param.getClass().getSimpleName().equals("Double")) ps.setDouble(x,Double.parseDouble(param.toString()));
            else if(param.getClass().getSimpleName().equals("Long")) ps.setLong(x,Long.parseLong(param.toString()));
            else if(param.getClass().getSimpleName().equals("BigDecimal")) ps.setBigDecimal(x,(BigDecimal)param);
            else if(param.getClass().getSimpleName().equals("Timestamp")) ps.setTimestamp(x, (Timestamp)param);
            x++;

        }
        }
        return ps;
    }

    private void closeConn(ResultSet rs, PreparedStatement pstmt){
        try {
            if(rs!=null)
            rs.close();
            if(pstmt!=null)
            pstmt.close();
        } catch (SQLException ex) {
            Logger.getLogger(AdminDB.class.getName()).log(Level.SEVERE, null, ex);
        }finally{
            rs=null;
            pstmt=null;
        }

    }

    private Object crearObjetos(Class clase,ResultSet resultado) throws InstantiationException, IllegalAccessException,InvocationTargetException{
        List<Atributo> atributos=this.obtenerAtributos(clase);
        Method metodo=null;
        //System.out.println("Clase::::"+clase.getCanonicalName());
        Object objeto=clase.newInstance();
        Object valor=null;
        for(Atributo atributo:atributos){
            try{
                
                if(atributo.getTipo()==1){
                    valor = resultado.getObject(atributo.getNombre());
                    if(valor!=null) { 
                        metodo = obtenerMetodo(clase,atributo.getNombre(),"set");                    
                        if(metodo!=null){
                            //System.out.println(metodo.toString()+" y argumento es: "+valor.getClass()+" y atributo: "+atributo.getNombre());
                            metodo.invoke(objeto, valor);
                        }
                    }
                }else{
                    Object objetoRecursivo=crearObjetos(atributo.getClase(),resultado);
                   // System.out.println(atributo.getClase().getCanonicalName());
                    if(objetoRecursivo!=null) metodo = obtenerMetodo(clase,atributo.getNombre(),"set");
                    if(metodo!=null) metodo.invoke(objeto, objetoRecursivo);
                }
            }catch (SQLException ex) { }
            catch(IllegalArgumentException ex){
                Logger.getLogger(AdminDB.class.getName()).log(Level.SEVERE, null, ex);
                //System.out.println(metodo.toString()+" y argumento es: "+valor.getClass());
                return null;
            }
        }
        return objeto;
    }
    private List<Atributo> obtenerAtributos(Class clase){
        List<Atributo> atributos=new ArrayList<Atributo>();
        Field[] fields=clase.getDeclaredFields();
        Atributo atributo;
        for(Field field:fields){
            atributo=new Atributo();
            if(field.getType().isPrimitive() || field.getType()==Date.class  ||field.getType()==String.class||field.getType()==BigDecimal.class||field.getType()==Timestamp.class||field.getType()==List.class ||field.getType()==ArrayList.class ||field.getType().isArray()){
                atributo.setNombre(field.getName());
                atributo.setTipo(1);
            }
            else {
                atributo.setNombre(field.getName());
                atributo.setTipo(2);
                atributo.setClase(field.getType());
            }
            atributos.add(atributo);
        }
        return atributos;
    }

   private Method obtenerMetodo(Class clase,String atributo,String tipoDeMetodo){
        Method[] metodos=clase.getDeclaredMethods();
        for(int index=0; index<metodos.length; index++){
            if(metodos[index].getName().equalsIgnoreCase(tipoDeMetodo+atributo)) return metodos[index];
        }
        return null;
    }

}
